USE [BS1]
GO
/****** Object:  View [dbo].[vw_VouchersDetail]    Script Date: 06/02/2017 09:47:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[vw_VouchersDetail]
AS
SELECT     dbo.VoucherDetails.VoucherID, dbo.Voucher.VoucherNo, dbo.Voucher.VoucherDate, dbo.VoucherDetails.AccountNo, Accounts_1.AccountTitle, 
                      dbo.SessionInfo.SessionTitle, dbo.VoucherDetails.Remarks, dbo.VoucherDetails.Amount AS Debit, 0 AS Credit, dbo.VoucherDetails.DrOrCr, 
                      dbo.VoucherDetails.VoucherDetailID, dbo.Voucher.Remarks AS MasterRemarks, dbo.Voucher.SessionID, Accounts_1.ParentAccount, 
                      dbo.Voucher.BankAccountID, dbo.Accounts.AccountTitle AS BankAccountTitle, ISNULL(dbo.Voucher.CustomerName, '') AS CustomerName, 
                      dbo.Voucher.Expense, dbo.Voucher.Discount, dbo.vw_Items.GroupID, dbo.VoucherDetails.ItemID, dbo.vw_Items.ItemName, dbo.VoucherDetails.Qty, 
                      dbo.VoucherDetails.Qty AS InQty, 0 AS OutQty, dbo.VoucherDetails.Rate, dbo.VoucherDetails.Margin, dbo.VoucherDetails.SRate
FROM         dbo.Accounts RIGHT OUTER JOIN
                      dbo.Voucher ON dbo.Accounts.AccountNo = dbo.Voucher.BankAccountID RIGHT OUTER JOIN
                      dbo.VoucherDetails LEFT OUTER JOIN
                      dbo.vw_Items ON dbo.VoucherDetails.ItemID = dbo.vw_Items.ItemID LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_1 ON dbo.VoucherDetails.AccountNo = Accounts_1.AccountNo ON 
                      dbo.Voucher.VoucherID = dbo.VoucherDetails.VoucherID LEFT OUTER JOIN
                      dbo.SessionInfo ON dbo.Voucher.SessionID = dbo.SessionInfo.SessionID
WHERE     (dbo.VoucherDetails.DrOrCr = N'Dr')
UNION ALL
SELECT     VoucherDetails_1.VoucherID, Voucher_1.VoucherNo, Voucher_1.VoucherDate, VoucherDetails_1.AccountNo, Accounts_1.AccountTitle, 
                      SessionInfo_1.SessionTitle, VoucherDetails_1.Remarks, 0 AS Debit, VoucherDetails_1.Amount AS Credit, VoucherDetails_1.DrOrCr, 
                      VoucherDetails_1.VoucherDetailID, Voucher_1.Remarks AS MasterRemarks, Voucher_1.SessionID, Accounts_1.ParentAccount, 
                      Voucher_1.BankAccountID, Accounts_2.AccountTitle AS BankAccountTitle, ISNULL(Voucher_1.CustomerName, '') AS CustomerName, 
                      Voucher_1.Expense, Voucher_1.Discount, vw_Items_1.GroupID, VoucherDetails_1.ItemID, vw_Items_1.ItemName, VoucherDetails_1.Qty, 0 AS InQty, 
                      VoucherDetails_1.Qty AS OutQty, VoucherDetails_1.Rate, VoucherDetails_1.Margin, VoucherDetails_1.SRate
FROM         dbo.Accounts AS Accounts_2 RIGHT OUTER JOIN
                      dbo.Voucher AS Voucher_1 ON Accounts_2.AccountNo = Voucher_1.BankAccountID RIGHT OUTER JOIN
                      dbo.VoucherDetails AS VoucherDetails_1 LEFT OUTER JOIN
                      dbo.vw_Items AS vw_Items_1 ON VoucherDetails_1.ItemID = vw_Items_1.ItemID LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_1 ON VoucherDetails_1.AccountNo = Accounts_1.AccountNo ON 
                      Voucher_1.VoucherID = VoucherDetails_1.VoucherID LEFT OUTER JOIN
                      dbo.SessionInfo AS SessionInfo_1 ON Voucher_1.SessionID = SessionInfo_1.SessionID
WHERE     (VoucherDetails_1.DrOrCr = N'Cr')


---------------------------------------------------------------------------

USE [BS1]
GO
/****** Object:  View [dbo].[vw_Stock]    Script Date: 06/02/2017 09:47:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_Stock]
AS
SELECT     dbo.VW_Vouchers.SessionID, dbo.VW_Vouchers.VoucherNo, dbo.VW_Vouchers.VoucherDate, dbo.VW_Vouchers.BankAccountID AS PartyID, 
                      dbo.VW_Vouchers.BankAccountTitle AS PartyName, dbo.VW_Vouchers.AccountNo, dbo.VW_Vouchers.AccountTitle, dbo.vw_Items.GroupID, 
                      dbo.vw_Items.ItemID, dbo.vw_Items.ItemName, SUM(ISNULL(dbo.VW_Vouchers.Qty, 0)) AS QTY
FROM         dbo.vw_Items LEFT OUTER JOIN
                      dbo.VW_Vouchers ON dbo.vw_Items.ItemID = dbo.VW_Vouchers.ItemID
WHERE     (dbo.VW_Vouchers.ItemID IS NOT NULL)
GROUP BY dbo.VW_Vouchers.BankAccountTitle, dbo.vw_Items.ItemName, dbo.VW_Vouchers.BankAccountID, dbo.VW_Vouchers.VoucherDate, 
                      dbo.vw_Items.ItemID, dbo.vw_Items.GroupID, dbo.VW_Vouchers.SessionID, dbo.VW_Vouchers.VoucherNo, dbo.VW_Vouchers.AccountNo, 
                      dbo.VW_Vouchers.AccountTitle

GO